package yangsw87.basic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
// ctrl + shift + O
import java.util.ArrayList;

//import java.sql.*; // 편하긴 편한데 가독성 측면에서는 좋지 않다.
// 내가 어떤 것을 사용하는지 모르기 때문

public class J22_BookDBDAO implements J22_BookDBService {

	// JDBC 접속 정보
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";

	// SQL문 작성
	private static final String insertSQL = " insert into BookDB values(BookDB_id.nextval,?,?,?,sysdate) ";
	private static final String selectSQL = " select * from BookDB order by id desc ";
	private static final String selectSQLbyID = " select * from BookDB where id=? order by id desc ";
	private static final String updateSQL = " update BookDB set title=?, publisher=?, author=?, regdate=sysdate where id=?";
	private static final String deleteSQL = " delete from BookDB where id=? ";

	@Override
	public int insertBookDB(J22_BookDBVO bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
			ps.setString(1, bvo.getTitle());
			ps.setString(2, bvo.getPublisher());
			ps.setString(3, bvo.getAuthor());

			cnt = ps.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null)
				try {
					ps.close();
				} catch (Exception ex) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception ex) {
				}
		}
		return cnt;
	}

	@Override
	public ArrayList<J22_BookDBVO> selectBookDB() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null; // 조회에서 추가됨
		// StringBuilder result = new StringBuilder();
		ArrayList<J22_BookDBVO> data = new ArrayList<>(); // 변경됨!!

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();

			// select문 실행 후 결과 처리 - 컬렉션 사용
			while (rs.next()) {
				J22_BookDBVO b = new J22_BookDBVO(rs.getString(2),
						rs.getString(3), rs.getString(4));
				b.setId(rs.getString(1)); // b에 id 추가
				b.setRegDate(rs.getString(5));
				data.add(b);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (Exception ex) {
				}
			if (ps != null)
				try {
					ps.close();
				} catch (Exception ex) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception ex) {
				}
		}
		return data;
	}

	@Override
	public J22_BookDBVO selectBookDBbyID(String id) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null; // 조회에서 추가됨
		// StringBuilder result = new StringBuilder();
		J22_BookDBVO b = null; // 변경됨!!

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQLbyID);
			ps.setString(1, id);

			rs = ps.executeQuery();

			while (rs.next()) {
				b = new J22_BookDBVO(rs.getString(2), rs.getString(3),
						rs.getString(4));
				b.setId(rs.getString(1)); // b에 id 추가
				b.setRegDate(rs.getString(5)); // b에 regdate 추가
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (Exception ex) {
				}
			if (ps != null)
				try {
					ps.close();
				} catch (Exception ex) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception ex) {
				}
		}
		return b;
	}

	@Override
	public int updateBookDB(J22_BookDBVO bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(updateSQL);

			ps.setString(1, bvo.getTitle());
			ps.setString(2, bvo.getPublisher());
			ps.setString(3, bvo.getAuthor());
			ps.setString(4, bvo.getId());

			cnt = ps.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null)
				try {
					ps.close();
				} catch (Exception ex) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception ex) {
				}
		}
		return cnt;
	}

	@Override
	public int deleteBookDB(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(deleteSQL);
			ps.setString(1, id);

			cnt = ps.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null)
				try {
					ps.close();
				} catch (Exception ex) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception ex) {
				}
		}
		return cnt;
	}

}
